package com.yjha.ai.demos.web.utils;

import com.yjha.ai.demos.web.value.APIValue;
import org.json.JSONArray;
import org.json.JSONObject;

import java.sql.*;

public class SQLExecutor {

    public static JSONObject executeSQL(APIValue apiValue) {
        String JDBC_URL = "jdbc:mysql://" + apiValue.getIp() + ":" + apiValue.getPort() + "/" + apiValue.getDbName();
        String JDBC_USERNAME = apiValue.getAccount();
        String JDBC_PASSWORD = apiValue.getPassword();

        JSONObject jsonObject = new JSONObject();
        Connection conn = null;

        try {
            // 连接数据库
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);

            // 执行 SQL 查询
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(apiValue.getSql())) {

                ResultSetMetaData rsmd = rs.getMetaData();
                int numColumns = rsmd.getColumnCount();

                if (!rs.isBeforeFirst()) {
                    // 如果结果集为空
                    jsonObject.put("results", new JSONArray());
                } else {
                    JSONArray resultsArray = new JSONArray();

                    while (rs.next()) {
                        JSONObject row = new JSONObject();
                        for (int i = 1; i <= numColumns; i++) {
                            String columnName = rsmd.getColumnName(i);
                            Object columnValue = rs.getObject(i);
                            row.put(columnName, columnValue);
                        }
                        resultsArray.put(row);
                    }

                    jsonObject.put("results", resultsArray);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return jsonObject;
    }
}